import glob
import xlrd
import json
import io


# excel表格转json文件
def excel_to_json(excel_file):
    # 加载工作薄
    book = xlrd.open_workbook(excel_file)
    sheet_names = book.sheet_names()
    print(sheet_names)
    alldict = dict()
    for index, sheet_name in enumerate(sheet_names):
        print("sheet_name =" + sheet_name)
        # if sheet_name.find('#') <= 0:
        #     print(sheet_name.find('#'))
        #     continue
        if sheet_name != "#recharge":
            print(11111)
            continue

        # 获取sheet页
        sheet = book.sheet_by_index(index)
        # 行数
        max_row = sheet.nrows
        # 列数
        max_column = sheet.ncols
        print(
            "excel_file:%s, sheet_name:%s,max_row: %d, max_column: %d"
            % (excel_file, sheet_name, max_row, max_column)
        )
        # 结果，数组存储
        result = []
        heads = []
        data_type = []
        use_type = []
        # 解析表头
        for column in range(max_column):
            # 读取的话行列是从（1，1）开始
            # header 字段名
            heads.append(sheet.cell(0, column).value)

            # 数据类型
            data_type.append(sheet.cell(1, column).value)

            # 区分前后端使用
            use_type.append(sheet.cell(2, column).value)

        newheaders = []
        for h in heads:
            if h in [
                "class",
                "type",
                "sub_type",
                "rmb",
                "show_name",
                "product_id",
                "qqgoods_id",
            ]:
                newheaders.append(h)
        # 遍历每一行
        for row in range(4, max_row):
            keyindex = 0

            one_line = {}
            # 获取每一行第一列
            first_cell = sheet.cell(row, 0)
            if first_cell.value == None or first_cell.value == "":
                continue
            for column in range(max_column):
                # 读取第二行开始每一个数据
                k = heads[column]
                if k not in newheaders:
                    continue
                dt = data_type[column]
                uset = use_type[column]
                if k == None:
                    continue
                if uset == "server":
                    continue

                # if uset  == "client" or uset == "none":
                # continue
                cell = sheet.cell(row, column)
                value = cell.value
                if k == "class":
                    keyindex = int(value)

                try:
                    if dt == "int":
                        if value == None or value == "":
                            value = 0
                        value = int(value)
                    if dt == "float":
                        if value == None:
                            value = 0
                        value = str(value)
                    if dt == "string":
                        if value == None or value == "":
                            value = ""
                        value = str(value)
                    if dt == "map" or dt == "maparr":
                        if value == None or value == "":
                            value = ""
                        else:
                            value = json.loads(value)
                    if dt == "term":
                        if (
                            value == None or value == ""
                        ):  # {1,1},{1,1} =[[1,1],[1,1]]   1,1
                            value = []  # [1,2,3]
                        else:
                            value = value.replace("{", "[")
                            value = value.replace("}", "]")
                            # value = value.split(",") #1,2 //{1,2}
                            # value = json.loads(value)
                            value = "[%s]" % (value)
                            value = json.loads(value)

                    if dt in ["bool", "boolean"]:
                        if value == None:
                            value = 0
                        if value == 0:
                            value = "false"
                        else:
                            value = "true"
                    if dt == "arr":
                        if value == "" or value == None:
                            value = []
                        if type(value) == type("s"):  # 1,2,3  =string  1= 1.0 =float
                            value = value.split(",")

                        if type(value) == type(0.1):
                            value = [int(value)]

                        res = list(map(int, value))
                        value = res

                    one_line[k] = value
                except:
                    # print("excel_file:%s, sheet_name:%s,row: %d, column: %d" % (excel_file,sheet_name,row, column))
                    raise ValueError(
                        "excel_file:%s, sheet_name:%s,row: %d, column: %d value:%s"
                        % (excel_file, sheet_name, row, column, value)
                    )
            print(one_line)
            try:
                oldLine = alldict[keyindex]
            except KeyError:
                oldLine = []
            oldLine.append(one_line)
            alldict[keyindex] = oldLine

        print(alldict)
        for index, index_c in alldict.items():
            fileName = "recharge_sub_type_%s.json" % index
            tmpDict = dict()
            for c in index_c:
                tmpDict[c["type"]] = c
            save_json_file(tmpDict, fileName)

        # json_file_name = "./{}.json".format(sheet_name.split("#")[1])
        # 将json保存为文件
        # save_json_file(alldict, json_file_name)


# 将json保存为文件
def save_json_file(jd, json_file_name):
    file = io.open(json_file_name, "w", encoding="utf-8")
    # 把对象转化为json对象
    # indent: 参数根据数据格式缩进显示，读起来更加清晰
    # ensure_ascii = True：默认输出ASCII码，如果把这个该成False, 就可以输出中文。
    txt = json.dumps(jd, indent=4, ensure_ascii=False)
    file.write(txt)
    file.close()


if "__main__" == __name__:
    excel_to_json("./recharge_data.xlsx")
    # excel_to_json(u'../../battle_data.xlsx')
    # excelslist = glob.glob("../../*.xls*")
    # for excel in excelslist:
    #     if "~$" not in excel:
    #         excel_to_json(excel)
    # excel_to_json(u'item_data.xlsx', 'item_data.json')
